﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Icoders.MarketTracker.Core.MasterData;
using Icoders.MarketTracker.Data.EF;
using Icoders.MarketTracker.Reports.Lib.Domain.Dashboard;
using Icoders.MarketTracker.Reports.Lib.Domain.Transactions;

namespace Icoders.MarketTracker.Reports.Lib.Repository.Transactions.Impl
{
    public class DashboardReportService : IDashboardReportService
    {
         private AppDataContext _ctx;

         public DashboardReportService(AppDataContext ctx)
        {
            _ctx = ctx;
        }
         public List<SalePerSku> GetSalePerSkuDashBoard(int clientid)
        {
            DateTime first = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.Where(s=>s.CreatedOn>=first).OrderBy(o => o.TblPurchase.CreatedOn);
            if(clientid!=0)
            {
                query = query.Where(s => s.TblProduct.TblClient.Id == clientid);
            }
            return query.GroupBy(g => new { g.ProductId, g.TblProduct.Name }).Select(s => new SalePerSku
            {
                Quantity = s.Sum(m => m.Quantity.Value * m.TblProduct.Milles.Value),
                Product = s.Key.Name
            }).OrderByDescending(o=>o.Quantity).Take(5).ToList();
        }

         public List<SalePerFSR> GetSalePerFsrDashBoard(int clientid)
        {
            DateTime first = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.Where(s => s.CreatedOn >= first).OrderBy(o => o.TblPurchase.CreatedOn);
            if (clientid != 0)
            {
                query = query.Where(s => s.TblProduct.TblClient.Id == clientid);
            }
            return query.GroupBy(g => new { g.TblPurchase.TblUser }).Select(s => new SalePerFSR
            {
                Quantity = s.Sum(m => m.Quantity.Value * m.TblProduct.Milles.Value),
                Fsr = s.Key.TblUser.Username
            }).OrderByDescending(o => o.Quantity).Take(5).ToList();
        }

         public List<SalePerFSR> GetBottomSalePerFsrDashBoard(int clientid)
        {
            DateTime first = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.Where(s => s.CreatedOn >= first).OrderBy(o => o.TblPurchase.CreatedOn);
            if (clientid != 0)
            {
                query = query.Where(s => s.TblProduct.TblClient.Id == clientid);
            }
            return query.GroupBy(g => new { g.TblPurchase.TblUser }).Select(s => new SalePerFSR
            {
                Quantity = s.Sum(m => m.Quantity.Value * m.TblProduct.Milles.Value),
                Fsr = s.Key.TblUser.Username
            }).OrderBy(o => o.Quantity).Take(5).ToList();
        }

         public List<CallageByPeriod> GetCallageDashBoard(int clientid)
        {
            IQueryable<TblPointOfPurchaseVist> query = _ctx.TblPointOfPurchaseVist.OrderBy(o => o.TblPurchase.CreatedOn);
            DateTime date = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            if (clientid != 0)
            {
                query = query.Where(s => s.TblPurchase.TblClient.Id == clientid);
            }
            var report = new List<CallageByPeriod>();
            report = query
                .Where(s => s.CreatedOn > date)
                .ToList().GroupBy(p => new { p.CreatedOn.Date }).OrderBy(o=>o.Key.Date)
                                  .ToList()
                                  .Select(s => new CallageByPeriod
                                  {
                                      SuccessfullCall = s.Count(x => x.IsSale),
                                      Call = s.Count(),
                                      EffectiveCallage = ((decimal)s.Count(x => x.IsSale) / s.Count())*100,
                                      Period = s.Key.Date.ToString("dd-MMM-yy")
                                  }).ToList();

            return report;
        }

         public List<CallageTargetByPeriod> GetCallageByCityDashBoard(int clientid, int cityId)
        {
            IQueryable<TblPointOfPurchaseVist> query = _ctx.TblPointOfPurchaseVist.OrderBy(o => o.TblPurchase.CreatedOn);
            DateTime date = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            if (clientid != 0)
            {
                query = query.Where(s => s.TblPurchase.TblClient.Id == clientid);
            }
            query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.Id == cityId);
            var report = new List<CallageTargetByPeriod>();
            report = query
                .Where(s => s.CreatedOn > date)
                .ToList().GroupBy(p => new { p.CreatedOn.Date }).OrderBy(o => o.Key.Date)
                                  .ToList()
                                  .Select(s => new CallageTargetByPeriod
                                  {
                                      SuccessfullCall = s.Count(x => x.IsSale),
                                      Call = s.Count(),
                                      Target =60,// ((decimal)s.Count(x => x.IsSale) / s.Count()) * 100,
                                      Period = s.Key.Date.ToString("dd-MMM-yy")
                                  }).ToList();

            return report;
        }

        public List<CallageEffectivenessByPeriod> GetCallageEffectivenessByCityDashBoard(int clientid, int cityId)
        {
            IQueryable<TblPointOfPurchaseVist> query = _ctx.TblPointOfPurchaseVist;
            IQueryable<TblPointOfPurchase> popquery = _ctx.TblPointOfPurchase;
            if (clientid != 0)
            {
                query = query.Where(s => s.TblPurchase.TblClient.Id == clientid);
                popquery = query.Select(s=>s.TblPurchase.TblPointOfPurchase);
            }
            DateTime date = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            query =query.Where(s => s.CreatedOn > date && s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.Id == cityId);
            
            var report = new List<CallageEffectivenessByPeriod>();
            decimal totalOutlet = popquery.Count(s => s.StatusId != (int) MasterEntityStatus.Deleted);
            report = query
                .Where(s => s.CreatedOn > date)
                .ToList().GroupBy(p => new { p.CreatedOn.Date }).OrderByDescending(o => o.Key.Date).OrderBy(o => o.Key.Date)
                                  .ToList()
                                  .Select(s => new CallageEffectivenessByPeriod
                                  {
                                      
                                      SoldTo = ((decimal)s.Where(a => a.IsSale).Select(o => o.TblPurchase.TblPointOfPurchase).Count() / totalOutlet)* 100,
                                      Target = 50,
                                      Visited=(s.Select(o=>o.TblPurchase.TblPointOfPurchase).Count()/totalOutlet)*100,
                                      Period = s.Key.Date.ToString("dd-MMM-yy")
                                  }).ToList();

            return report;
            
            
            
        }

        public List<SalePerSkuPerPeriod> GetSalePerSkuPerPeriodDashBoard(int clientid)
        {
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.OrderBy(o => o.TblPurchase.CreatedOn);
            if (clientid != 0)
            {
                query = query.Where(s => s.TblProduct.TblClient.Id == clientid);
            }
            DateTime date = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);

            var report = new List<SalePerSkuPerPeriod>();
            report = query
                .Where(s => s.CreatedOn > date)
                .ToList().GroupBy(p => new { p.CreatedOn.Date,p.TblProduct }).OrderByDescending(o => o.Key.Date).OrderBy(o => o.Key.Date)
                                  .ToList()
                                  .Select(s => new SalePerSkuPerPeriod
                                  {
                                      Product = s.Key.TblProduct.Name,
                                      Quantity = s.Sum(m => m.Quantity.Value * m.TblProduct.Milles.Value),
                                      Period = s.Key.Date.ToString("dd-MMM-yy")
                                  }).ToList();

            return report;
        }

         public List<SalePerSkuPerPeriod> GetSalePerBrandPerPeriodDashBoard(int clientid)
        {
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.OrderBy(o => o.TblPurchase.CreatedOn);
            if (clientid != 0)
            {
                query = query.Where(s => s.TblProduct.TblClient.Id == clientid);
            }
            DateTime date = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);

            var report = new List<SalePerSkuPerPeriod>();
            report = query
                .Where(s => s.CreatedOn > date)
                .ToList().GroupBy(p => new { p.CreatedOn.Date, p.TblProduct.TblProductBrand }).OrderByDescending(o => o.Key.Date).OrderBy(o => o.Key.Date)
                                  .ToList()
                                  .Select(s => new SalePerSkuPerPeriod
                                  {
                                      Product = s.Key.TblProductBrand.Name,
                                      Quantity = s.Sum(m => m.Quantity.Value * m.TblProduct.Milles.Value),
                                      Period = s.Key.Date.ToString("dd-MMM-yy")
                                  }).ToList();

            return report;
        }

         public List<OutletPerformancePerPeriod> GetOutletPerformancePeriodDashBoard(int clientid)
        {
            IQueryable<TblPurchase> query = _ctx.TblPurchase;
            if (clientid != 0)
            {
                query = query.Where(s => s.TblClient.Id == clientid);
            }
            DateTime date = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            int allOutlet = _ctx.TblPointOfPurchase.Count(s => s.StatusId != (int)MasterEntityStatus.Deleted);
            var report = new List<OutletPerformancePerPeriod>();
            report = query
                .Where(s => s.CreatedOn > date)
                .ToList().GroupBy(p => new { p.CreatedOn.Date }).OrderByDescending(o => o.Key.Date).OrderBy(o => o.Key.Date)
                                  .ToList()
                                  .Select(s => new OutletPerformancePerPeriod
                                  {
                                      All = allOutlet,
                                      HadaSale = s.Count(o => o.TblPointOfPurchaseVist.Any(x => x.IsSale)),
                                      Visted = s.Count(o => o.TblPointOfPurchaseVist.Any()),
                                      Period = s.Key.Date.ToString("dd-MMM-yy")
                                  }).ToList();

            return report;
        }

         public List<SalesValuePerPeriod> GetSalesValuePerPeriodDashBoard(int clientid)
        {
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem;
            if (clientid != 0)
            {
                query = query.Where(s => s.TblProduct.TblClient.Id == clientid);
            }
            DateTime date = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            var report = new List<SalesValuePerPeriod>();
            report = query
                .Where(s => s.CreatedOn > date)
                .ToList().GroupBy(p => new { p.CreatedOn.Date }).OrderByDescending(o => o.Key.Date).OrderBy(o => o.Key.Date)
                                  .ToList()
                                  .Select(s => new SalesValuePerPeriod
                                  {
                                      Value = (decimal) s.Sum(o=>o.Price*o.Quantity),
                                      Period = s.Key.Date.ToString("dd-MMM-yy")
                                  }).ToList();

            return report;
        }

         public List<SalesValuePerArea> GetSalesValuePerAreaDashBoard(int clientid)
        {
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem;
            if (clientid != 0)
            {
                query = query.Where(s => s.TblProduct.TblClient.Id == clientid);
            }
            DateTime date = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            var report = new List<SalesValuePerArea>();
            report = query
                .Where(s => s.CreatedOn > date)
                .GroupBy(p => new { p.TblPurchase.TblPointOfPurchase.TblZone.TblArea }).OrderBy(o => o.Key.TblArea.Name)
                                  .ToList()
                                  .Select(s => new SalesValuePerArea
                                  {
                                      Value = (decimal)s.Sum(o => o.Price * o.Quantity),
                                      Area = s.Key.TblArea.Name
                                  }).OrderByDescending(o=>o.Value).Take(5).ToList();

            return report;
        }

         public List<SalesValuePerArea> GetBottomSalesValuePerAreaDashBoard(int clientid)
        {
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem;
            if (clientid != 0)
            {
                query = query.Where(s => s.TblProduct.TblClient.Id == clientid);
            }
            DateTime date = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            var report = new List<SalesValuePerArea>();
            report = query
                .Where(s => s.CreatedOn > date)
                .GroupBy(p => new { p.TblPurchase.TblPointOfPurchase.TblZone.TblArea }).OrderBy(o => o.Key.TblArea.Name)
                                  .ToList()
                                  .Select(s => new SalesValuePerArea
                                  {
                                      Value = (decimal)s.Sum(o => o.Price * o.Quantity),
                                      Area = s.Key.TblArea.Name
                                  }).OrderBy(o => o.Value).Take(5).ToList();

            return report;
        }

         public List<SalesValueAvgPerPeriod> GetSalesValueAvgPerPeriodDashBoard(int clientid)
        {
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem;
            if (clientid != 0)
            {
                query = query.Where(s => s.TblProduct.TblClient.Id == clientid);
            }
            DateTime date = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            var report = new List<SalesValueAvgPerPeriod>();
            report = query
                .Where(s => s.CreatedOn > date)
                .ToList().GroupBy(p => new { p.CreatedOn.Date,p.TblPurchase.TblUser,p.TblProduct.TblClient }).OrderBy(o => o.Key.Date)
                                  .ToList()
                                  .Select(s => new 
                                  {
                                      Total=(decimal) s.Sum(o => o.Price * o.Quantity),
                                      Period = s.Key.Date.ToString("dd-MMM-yy"),
                                      Target=s.Key.TblClient.SalesmanTarget
                                  }).GroupBy(g=> new {g.Period,g.Target})
                                  .Select(x=> new SalesValueAvgPerPeriod
                                                  {
                                                     Average=x.Average(a=>a.Total) ,
                                                     Period=x.Key.Period,
                                                     Target=x.Key.Target
                                                  }
                                  ).ToList();

            return report;
        }

         public List<ProductAvalibilty> GetProductAvalibiltyDashBoard(int clientid)
        {
            List<ProductAvalibilty> result = new List<ProductAvalibilty>();
              DateTime date = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            IQueryable<TblPurchase> query = _ctx.TblPurchase.Where(s => s.CreatedOn > date);
            if (clientid != 0)
            {
                query = query.Where(s => s.TblClient.Id == clientid);
            }
            decimal visted = query.Select(s => s.TblPointOfPurchase.Id).Distinct().Count();
             List<int> reasons = _ctx.TblReason.Where(w => w.StatusId != 3 && w.ReasonTypeId == 2).Select(s => s.Id).ToList();
            reasons.Add(0);
            foreach(var product in _ctx.TblProduct)
            {
               // int sold = query.Where(p=>p.TblPurchaseItem.Any(a=>a.ProductId==product.Id && a.ReasonId==0)).Select(s => s.TblPointOfPurchase.Id).Distinct().Count();
                decimal hasstock = query.Where(p => p.TblPurchaseItem.Any(a => a.ProductId == product.Id && reasons.Contains(a.ReasonId))).Select(s => s.TblPointOfPurchase.Id).Distinct().Count();
                ProductAvalibilty pa = new ProductAvalibilty();
                pa.Product = product.Name;
                pa.Available= (hasstock/visted)*100;
                result.Add(pa);
            }
            return result.OrderBy(s => s.Product).ToList();
            //IQueryable<TblPointOfPurchase> query = _ctx.TblPointOfPurchase;
          
           
           
            //var repo = query.Where(s=>s.TblPurchase.).Where(s => s.CreatedOn > date)
            //    .GroupBy(
            //        g =>
            //        new { g.TblPurchase.TblPurchaseItem.})
            //    .Select(s => new ProductAvalibilty
            //    {
                    
            //        Product = s.Key.TblProduct.Name,
            //        Available = ((decimal)(s.Count(x => x.ReasonId == 0) + s.Count(x => reasons.Contains(x.ReasonId))) / s.Select(x => x.TblPurchase.TblPointOfPurchaseVist).Count())*100,//_ctx.TblPointOfPurchase.Where(z=>z.TblZone.TblArea.TblFrsAssign.Any(a=>a.TblUser.Id==s.Key.TblUser.Id)).Count(),
            //    }).OrderBy(s=>s.Product).ToList();

            //return repo.ToList();
            
        }

         public List<Penetration> GetPenetrationDashBoard(int clientid)
        {
            IQueryable<TblPointOfPurchaseVist> query = _ctx.TblPointOfPurchaseVist;
            if (clientid != 0)
            {
                query = query.Where(s => s.TblPurchase.TblClient.Id == clientid);
            }
            DateTime date = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            var report = new List<Penetration>();
            query = query.Where(s => s.CreatedOn > date);
            report.Add(new Penetration {Status = "Visited", Count = query.GroupBy(g=>g.TblPurchase.TblPointOfPurchase).Count()});
            report.Add(new Penetration { Status = "SoldTo", Count = query.Where(a => a.IsSale).GroupBy(g => g.TblPurchase.TblPointOfPurchase).Count() });
            var outletvisted = query.Select(s => s.TblPurchase.TblPointOfPurchase.Id).ToList();
            int noofoutletnevervisted = _ctx.TblPointOfPurchase.Count(s => !outletvisted.Contains(s.Id));
            report.Add(new Penetration { Status = "NoVisited", Count = noofoutletnevervisted });
            return report.ToList();
        }

        public List<SalePerSkuPerArea> GetSalePerSkuPerAreaDashBoard(int clientid)
        {
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.OrderBy(o => o.TblPurchase.CreatedOn);
            if (clientid != 0)
            {
                query = query.Where(s => s.TblProduct.TblClient.Id == clientid);
            }
            DateTime date = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);

            var report = new List<SalePerSkuPerArea>();
            report = query
                .Where(s => s.CreatedOn > date)
                .GroupBy(p => new { p.TblPurchase.TblPointOfPurchase.TblZone.TblArea, p.TblProduct }).OrderBy(o => o.Key.TblArea.Name)
                                  .ToList()
                                  .Select(s => new SalePerSkuPerArea
                                  {
                                      Product = s.Key.TblProduct.Name,
                                      Quantity = s.Sum(m => m.Quantity.Value * m.TblProduct.Milles.Value),
                                      Area = s.Key.TblArea.Name
                                  }).ToList();

            return report;
        }

        public List<SalePerSku> GetSkuVolumeDashBoard(int clientid)
        {
            DateTime first = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.Where(s => s.CreatedOn >= first).OrderBy(o => o.TblPurchase.CreatedOn);
            if (clientid != 0)
            {
                query = query.Where(s => s.TblProduct.TblClient.Id == clientid);
            }
            return query.GroupBy(g => new { g.ProductId, g.TblProduct.Name }).Select(s => new SalePerSku
            {
                Quantity = s.Sum(m => m.Quantity.Value * m.TblProduct.Milles.Value),
                Product = s.Key.Name
            }).OrderByDescending(o => o.Quantity).ToList();
        }

        public List<SalePerSku> GetSkuVolumeByCityDashBoard(int clientid, int cityid)
        {
            DateTime first = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.Where(s => s.CreatedOn >= first).OrderBy(o => o.TblPurchase.CreatedOn);
            if (clientid != 0)
            {
                query = query.Where(s => s.TblProduct.TblClient.Id == clientid);
            }
            query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.Id == cityid);
            return query.GroupBy(g => new { g.ProductId, g.TblProduct.Name }).Select(s => new SalePerSku
            {
                Quantity = s.Sum(m => m.Quantity.Value * m.TblProduct.Milles.Value),
                Product = s.Key.Name
            }).OrderByDescending(o => o.Quantity).ToList();
        }

        public Dictionary<string, string> GetCityWithData(int clientid)
        {
            DateTime first = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.Where(s => s.CreatedOn >= first).OrderBy(o => o.TblPurchase.CreatedOn);
            if (clientid != 0)
            {
                query = query.Where(s => s.TblProduct.TblClient.Id == clientid);
            }
            return
                query.Select(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity).Select(
                    s => new {s.Id, s.Name}).Distinct().ToDictionary(r=>r.Id.ToString(),r=>r.Name);
        }

        public List<TotalSalePerPeriod> GetDailySkuVolumeDashBoard(int clientid)
        {
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem;
            if (clientid != 0)
            {
                query = query.Where(s => s.TblProduct.TblClient.Id == clientid);
            }
            DateTime date = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            var report = new List<TotalSalePerPeriod>();
            report = query
                .Where(s => s.CreatedOn > date)
                .ToList().GroupBy(p => new { p.CreatedOn.Date }).OrderByDescending(o => o.Key.Date).OrderBy(o => o.Key.Date)
                                  .ToList()
                                  .Select(s => new TotalSalePerPeriod
                                  {
                                      Quantity =  s.Sum(m => m.Quantity.Value * m.TblProduct.Milles.Value),
                                      Period = s.Key.Date.ToString("dd-MMM-yy")
                                  }).ToList();

            return report;
        }

        public List<SalePerSkuPerCity> GetSalePerSkuPerCityDashBoard(int clientid)
        {
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.OrderBy(o => o.TblPurchase.CreatedOn);
            if (clientid != 0)
            {
                query = query.Where(s => s.TblProduct.TblClient.Id == clientid);
            }
            DateTime date = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);

            var report = new List<SalePerSkuPerCity>();
            report = query
                .Where(s => s.CreatedOn > date)
                .GroupBy(p => new { p.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity, p.TblProduct }).OrderBy(o => o.Key.TblCity.Name)
                                  .ToList()
                                  .Select(s => new SalePerSkuPerCity
                                  {
                                      Product = s.Key.TblProduct.Name,
                                      Quantity = s.Sum(m => m.Quantity.Value * m.TblProduct.Milles.Value),
                                      City = s.Key.TblCity.Name
                                  }).ToList();

            return report;
        }
    }
}
